﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Data.SqlClient;
using System.IO;

namespace TCCT_CTC_CSDLDangVienV1.HeThong
{
    public partial class frmConfig : frmBase
    {
        public frmConfig()
        {
            InitializeComponent();
        }

        private void frmConfig_Load(object sender, EventArgs e)
        {
            UI.TextBox.ApplyPassword(txtDBDPassword);
            UI.TextBox.ApplyDefault(txtDBDServerName);
            UI.TextBox.ApplyDefault(txtDBDUserName);
            UI.TextBox.ApplyDefault(txtDBDDatabaseName);


            DataTable configDT = new DataTable();
            configDT = OleDBClass.OleReturnDataTable("Select * From tblConfig");

            if (configDT.Rows[0]["TrustConnect"].ToString() == "True")
            {
                chTuDong.Checked = true;
                txtDBDServerName.Text = configDT.Rows[0]["DBDServerName"].ToString();

                txtDBDDatabaseName.Text = configDT.Rows[0]["DBDDatabaseName"].ToString();

            }
            else
            {
                chTuDong.Checked = false;
                txtDBDServerName.Text = configDT.Rows[0]["DBDServerName"].ToString();

                txtDBDDatabaseName.Text = configDT.Rows[0]["DBDDatabaseName"].ToString();

                txtDBDUserName.Text = configDT.Rows[0]["DBDUserName"].ToString();
                txtDBDPassword.Text = EncryptSMSB.Decrypt(configDT.Rows[0]["DBDPassword"].ToString());
            }
        }

        private void chTuDong_CheckedChanged(object sender, EventArgs e)
        {
            txtDBDUserName.Enabled = !chTuDong.Checked;
            txtDBDPassword.Enabled = !chTuDong.Checked;
        }

        private void btnKetNoi_Click(object sender, EventArgs e)
        {
            try
            {
                if (chTuDong.Checked)
                {
                    if (SqlHelper.ConnectDatabase(txtDBDServerName.Text.Trim(), txtDBDDatabaseName.Text.Trim()))
                    {
                        ShowInfoMessage("Kết nối thành công");

                        string sql = "Update tblConfig Set " +
                                     "DBDServerName =@DBDServerName, " +
                                     "DBDUserName = @DBDUserName, " +
                                     "DBDPassword = @DBDPassword, " +
                                     "DBDDatabaseName = @DBDDatabaseName, " +
                                     "TrustConnect = @TrustConnect ";

                        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, OleDBClass.OleConn);
                        cmd.Parameters.Add("@DBDServerName", System.Data.OleDb.OleDbType.VarWChar).Value = txtDBDServerName.Text.Trim();
                        cmd.Parameters.Add("@DBDUserName", System.Data.OleDb.OleDbType.VarWChar).Value = txtDBDUserName.Text.Trim();
                        cmd.Parameters.Add("@DBDPassword", System.Data.OleDb.OleDbType.VarWChar).Value = EncryptSMSB.Encrypt(txtDBDPassword.Text);
                        cmd.Parameters.Add("@DBDDatabaseName", System.Data.OleDb.OleDbType.VarWChar).Value = txtDBDDatabaseName.Text.Trim();
                        cmd.Parameters.Add("@TrustConnect", System.Data.OleDb.OleDbType.Boolean).Value = chTuDong.Checked;

                        if (OleDBClass.OleConn.State != ConnectionState.Open)
                            OleDBClass.OleConn.Open();
                        cmd.ExecuteNonQuery();
                        ShowInfoMessage("Lưu cấu hình kết nối thành công");
                    }
                    else
                        ShowInfoMessage("Kết nối không thành công");
                }
                else
                {
                    if (SqlHelper.ConnectDatabase(txtDBDServerName.Text.Trim(), txtDBDUserName.Text.Trim(), txtDBDPassword.Text, txtDBDDatabaseName.Text.Trim()))
                    {
                        ShowInfoMessage("Kết nối thành công");

                        string sql = "Update tblConfig Set " +
                                     "DBDServerName =@DBDServerName, " +
                                     "DBDUserName = @DBDUserName, " +
                                     "DBDPassword = @DBDPassword, " +
                                     "DBDDatabaseName = @DBDDatabaseName, " +
                                     "TrustConnect = @TrustConnect ";

                        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sql, OleDBClass.OleConn);
                        cmd.Parameters.Add("@DBDServerName", System.Data.OleDb.OleDbType.VarWChar).Value = txtDBDServerName.Text.Trim();
                        cmd.Parameters.Add("@DBDUserName", System.Data.OleDb.OleDbType.VarWChar).Value = txtDBDUserName.Text.Trim();
                        cmd.Parameters.Add("@DBDPassword", System.Data.OleDb.OleDbType.VarWChar).Value = EncryptSMSB.Encrypt(txtDBDPassword.Text);
                        cmd.Parameters.Add("@DBDDatabaseName", System.Data.OleDb.OleDbType.VarWChar).Value = txtDBDDatabaseName.Text.Trim();
                        cmd.Parameters.Add("@TrustConnect", System.Data.OleDb.OleDbType.Boolean).Value = chTuDong.Checked;

                        if (OleDBClass.OleConn.State != ConnectionState.Open)
                            OleDBClass.OleConn.Open();
                        cmd.ExecuteNonQuery();
                        ShowInfoMessage("Lưu cấu hình kết nối thành công");
                    }
                    else
                        ShowInfoMessage("Kết nối không thành công");
                }

            }
            catch (Exception _ex)
            {
                ShowErrorMessage(_ex);
            }
        }

        private void btnAttach_Click(object sender, EventArgs e)
        {
            try
            {
                #region kiem tra xem da ket noi dc chua

                if (chTuDong.Checked)
                {
                    if (SqlHelper.ConnectDatabase(txtDBDServerName.Text.Trim(), txtDBDDatabaseName.Text.Trim()))
                    {
                        MyCommon.ShowMessage("Dữ liệu đã attach");

                        return;
                    }

                }
                else
                {
                    if (SqlHelper.ConnectDatabase(txtDBDServerName.Text.Trim(), txtDBDUserName.Text.Trim(), txtDBDPassword.Text, txtDBDDatabaseName.Text.Trim()))
                    {
                        MyCommon.ShowMessage("Dữ liệu đã attach.");

                        return;
                    }

                }


                #endregion kiem tra xem da ket noi dc chua

                #region copy data sang ổ D
                string mdfFile = "D:\\EXIMVN\\Data\\Eximsales.mdf";
                string ldfFile = "D:\\EXIMVN\\Data\\EximSales_log.LDF";

                if (!Directory.Exists("D:\\EXIMVN\\Data"))
                    Directory.CreateDirectory("D:\\EXIMVN\\Data");

                if (!File.Exists(mdfFile))
                {
                    File.Copy(AppDomain.CurrentDomain.BaseDirectory + "Data\\EximSales.MDF", mdfFile);
                }
                if (!File.Exists(ldfFile))
                {
                    File.Copy(AppDomain.CurrentDomain.BaseDirectory + "Data\\EximSales_log.LDF", ldfFile);
                }


                #endregion copy data sang ổ D

                #region Attach database đã nào.


                try
                {

                    SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
                    if (chTuDong.Checked)
                    {
                        srv.Name = this.txtDBDServerName.Text.Trim();
                        srv.LoginSecure = true;
                        srv.Connect(null, null, null);
                    }
                    else
                        srv.Connect(this.txtDBDServerName.Text.Trim(), this.txtDBDUserName.Text.Trim(), this.txtDBDPassword.Text);
                    srv.AttachDB(txtDBDDatabaseName.Text.Trim(), mdfFile);
                    srv.Close();

                }
                catch
                {

                    if (File.Exists(ldfFile))
                    {
                        try
                        {
                            File.Delete(ldfFile);
                        }
                        catch
                        {
                        }
                    }
                    try
                    {
                        SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
                        if (chTuDong.Checked)
                        {
                            srv.Name = this.txtDBDServerName.Text.Trim();
                            srv.LoginSecure = true;
                            srv.Connect(null, null, null);
                        }
                        else
                            srv.Connect(this.txtDBDServerName.Text.Trim(), this.txtDBDUserName.Text.Trim(), this.txtDBDPassword.Text);
                        srv.AttachDB(txtDBDDatabaseName.Text.Trim(), mdfFile);
                        srv.Close();
                    }
                    catch
                    {
                        #region attach vào verver
                        if (!File.Exists(ldfFile))
                        {
                            File.Copy(AppDomain.CurrentDomain.BaseDirectory + "Data\\EximSales_log.LDF", ldfFile);
                        }

                        SqlConnection conn = new SqlConnection("Server=" + txtDBDServerName.Text.Trim() + "; Data Source=;Integrated Security=SSPI");
                        SqlCommand cmd1 = new SqlCommand("", conn);
                        cmd1.CommandText = "SELECT name FROM sys.databases WHERE name = '" + txtDBDDatabaseName.Text.Trim() + "'";

                        conn.Open();

                        SqlDataReader rdr = cmd1.ExecuteReader();
                        bool bAttach = true;
                        while (rdr.Read())
                        {
                            if (rdr.GetString(0) == txtDBDDatabaseName.Text.Trim())
                            {
                                bAttach = false;
                                break;
                            }
                        }
                        rdr.Dispose();
                        if (bAttach)
                        {
                            #region Attach
                            cmd1 = new SqlCommand("", conn);
                            cmd1.CommandText = "exec sys.sp_attach_db " + txtDBDDatabaseName.Text.Trim() + ", '" + AppDomain.CurrentDomain.BaseDirectory + "Data\\EximSales.MDF" + "', '" + AppDomain.CurrentDomain.BaseDirectory + "Data\\EximSales_log.LDF" + "'";

                            cmd1.ExecuteNonQuery();

                            #endregion Attach
                        }
                        cmd1.Dispose();
                        conn.Dispose();
                        conn.Close();
                        #endregion attach vào verver
                    }
                }
                MyCommon.ShowMessage("Thực hiện xong");
                #endregion Attach database đã nào.
            }
            catch (Exception _ex)
            {
                MyCommon.ShowMessage(_ex);
            }
        }

        private void btnDetach_Click(object sender, EventArgs e)
        {
            try
            {
                try
                {
                    SqlHelper.ExecuteNonQuery(CommandType.Text, "ALTER DATABASE " + txtDBDDatabaseName.Text.Trim() + " SET OFFLINE WITH ROLLBACK IMMEDIATE ALTER DATABASE " + txtDBDDatabaseName.Text.Trim() + " SET ONLINE WITH ROLLBACK IMMEDIATE ");
                }
                catch { }

                SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
                if (chTuDong.Checked)
                {
                    srv.Name = this.txtDBDServerName.Text.Trim();
                    srv.LoginSecure = true;
                    srv.Connect(null, null, null);
                }
                else
                    srv.Connect(this.txtDBDServerName.Text.Trim(), this.txtDBDUserName.Text.Trim(), this.txtDBDPassword.Text);
                srv.DetachDB(txtDBDDatabaseName.Text.Trim(), false);
                srv.Close();
                MyCommon.ShowMessage("Thực hiện xong");
            }
            catch (Exception _ex)
            {
                MyCommon.ShowMessage(_ex);
            }
        }

        private void btnBackup_Click(object sender, EventArgs e)
        {
            try
            {
                SaveFileDialog sfd = new SaveFileDialog();

                sfd.FileName = "SaoLuuDuLieuBanHang_" + DateTime.Now.ToString("dd_MM_yyyy") + ".bak";
                if (sfd.ShowDialog() == DialogResult.Cancel)
                    return;

                string saveFile = sfd.FileName.Replace(".bak", "");

                if (saveFile.Length == 0) return;
                //create an instance of a server class
                saveFile += ".bak";

                SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
                //connect to the server

                if (chTuDong.Checked)
                {
                    srv.Name = this.txtDBDServerName.Text.Trim();
                    srv.LoginSecure = true;
                    srv.Connect(null, null, null);
                }
                else
                    srv.Connect(this.txtDBDServerName.Text.Trim(), this.txtDBDUserName.Text.Trim(), this.txtDBDPassword.Text);


                //create a backup class instance
                SQLDMO.Backup bak = new SQLDMO.BackupClass();
                //set the backup device = files property ( easy way )
                bak.Devices = bak.Files;
                //set the files property to the File Name text box
                bak.Files = saveFile;
                //set the database to the chosen database
                bak.Database = txtDBDDatabaseName.Text.Trim();
                //perform the backup
                bak.SQLBackup(srv);
                srv.Close();
                MyCommon.ShowMessage("Thực hiện xong");
            }
            catch (Exception err)
            {
                MyCommon.ShowMessage(err);
            }
        }


        public void btnBackupTuDong_Click(object sender, EventArgs e)
        {
            try
            {
                #region load config db

                DataTable configDT = new DataTable();
                configDT = OleDBClass.OleReturnDataTable("Select * From tblConfig");

                if (configDT.Rows[0]["TrustConnect"].ToString() == "True")
                {
                    chTuDong.Checked = true;
                    txtDBDServerName.Text = configDT.Rows[0]["DBDServerName"].ToString();

                    txtDBDDatabaseName.Text = configDT.Rows[0]["DBDDatabaseName"].ToString();

                }
                else
                {
                    chTuDong.Checked = false;
                    txtDBDServerName.Text = configDT.Rows[0]["DBDServerName"].ToString();

                    txtDBDDatabaseName.Text = configDT.Rows[0]["DBDDatabaseName"].ToString();

                    txtDBDUserName.Text = configDT.Rows[0]["DBDUserName"].ToString();
                    txtDBDPassword.Text = EncryptSMSB.Decrypt(configDT.Rows[0]["DBDPassword"].ToString());
                }
                #endregion load config db

                string saveFile = Application .StartupPath +"\\Data\\SaoLuu\\SaoLuuDuLieuBanHang_" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss") + ".bak";



                SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
                //connect to the server

                if (chTuDong.Checked)
                {
                    srv.Name = this.txtDBDServerName.Text.Trim();
                    srv.LoginSecure = true;
                    srv.Connect(null, null, null);
                }
                else
                    srv.Connect(this.txtDBDServerName.Text.Trim(), this.txtDBDUserName.Text.Trim(), this.txtDBDPassword.Text);


                //create a backup class instance
                SQLDMO.Backup bak = new SQLDMO.BackupClass();
                //set the backup device = files property ( easy way )
                bak.Devices = bak.Files;
                //set the files property to the File Name text box
                bak.Files = saveFile;
                //set the database to the chosen database
                bak.Database = txtDBDDatabaseName.Text.Trim();
                //perform the backup
                bak.SQLBackup(srv);
                srv.Close();
               
            }
            catch (Exception err)
            {
                MyCommon.ShowMessage(err);
            }
        }


        private void btnRestore_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.ShowDialog();

                string openFile = ofd.FileName;


                if (openFile.Length == 0) return;

                try
                {
                    SqlHelper.ExecuteNonQuery(CommandType.Text, "ALTER DATABASE " + txtDBDDatabaseName.Text.Trim() + " SET OFFLINE WITH ROLLBACK IMMEDIATE ALTER DATABASE " + txtDBDDatabaseName.Text.Trim() + " SET ONLINE WITH ROLLBACK IMMEDIATE ");
                }
                catch { }

                //create an instance of a server class
                SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
                //connect to the server
                if (chTuDong.Checked)
                {
                    srv.Name = this.txtDBDServerName.Text.Trim();
                    srv.LoginSecure = true;
                    srv.Connect(null, null, null);
                }
                else
                    srv.Connect(this.txtDBDServerName.Text.Trim(), this.txtDBDUserName.Text.Trim(), this.txtDBDPassword.Text);
                //create a restore class instance
                SQLDMO.Restore res = new SQLDMO.RestoreClass();
                //set the backup device = files property ( easy way )
                res.Devices = res.Files;
                //set the files property to the File Name text box
                res.Files = openFile;
                //set the database to the chosen database
                res.Database = txtDBDDatabaseName.Text.Trim();
                // Restore the database
                res.ReplaceDatabase = true;

                res.SQLRestore(srv);
                MyCommon.ShowMessage("Thực hiện xong");
            }
            catch (Exception err)
            {

                MyCommon.ShowMessage(err);
            }
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            try
            {
                this.cboDatabase.Properties.Items.Clear();
                SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();

                if (chTuDong.Checked)
                {
                    srv.Name = this.txtDBDServerName.Text.Trim();
                    srv.LoginSecure = true;
                    srv.Connect(null, null, null);
                }
                else
                    srv.Connect(this.txtDBDServerName.Text.Trim(), this.txtDBDUserName.Text.Trim(), this.txtDBDPassword.Text);

                foreach (SQLDMO.Database db in srv.Databases)
                {
                    if (db.Name != null)
                        this.cboDatabase.Properties.Items.Add(db.Name);
                }
                this.cboDatabase.Properties.Sorted = true;
                if (this.cboDatabase.Properties.Items.Count > 0)
                {


                }
                else
                {

                    this.cboDatabase.Text = "<Không tìm thấy cơ sở dữ liệu nào>";
                }
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message, "Loi");
            }
        }

        private void simpleButton2_Click(object sender, EventArgs e)
        {
            SqlHelper.ExecuteNonQuery(CommandType.Text, "CREATE TABLE tblHangHoa_Set (ID bigint identity(1,1) PRIMARY KEY ,SetID bigint,PCSID bigint,PCSNumber float)");
            MyCommon.ShowMessage("Ok rồi");
        }

    }
}